﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetTaskClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetTaskClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetTaskClass]
    @UserName nvarchar(255),
    @RightCode int = 10
/*

============================================================
功能:    得到@UserName的所有任务
参数:
    @RightCode    int    :    任务代表的权限包ID
============================================================

*/
AS
BEGIN
SET NOCOUNT ON

DECLARE @Staff_id int

SET @staff_id=0

--取得用户的ID
SELECT @staff_id = staff_id FROM uds_staff WHERE staff_name = @username;

--创建临时类表
DECLARE @class TABLE (obj_id int);

--插入作为成员的组ID
IF ( @staff_id > 0 )
BEGIN
    INSERT INTO @class
        SELECT team_id FROM uds_staff_in_team WHERE staff_id = @staff_id
END

--得到拥有的类信息
SELECT
        *,
        (
        SELECT count(*)
            FROM
                uds_document
            WHERE
                classid = a.classid
                and docapproved = 1
                and doctype = 0
        ) AS FILECOUNT
    FROM
        uds_class a
        left outer join uds_teaminfo b on a.classid = b.teamid
    WHERE
        a.classid in ( SELECT obj_id  FROM  @class )
        and a.ClassID <> a.ClassParentID
    ORDER BY b.enddate DESC

END
GO